Advanced SQL - Relational Set Operators
What are Relational Set Operators? There are three primary SQL commands involved when implementing a Union, Intersection and difference relational operators. As you may know, SQL data manipulation commands are set-oriented which are involved in operating over entire sets of rows and columns in tables at once. The Union, Intersect, and Minus statements make sure these operations occur. The Union, Intersect, and Minus are the names of the SQL statements implemented in Oracle. Union, Intersect and Minus only work properly if relations are Union-compatible, which is based off the names of the relation attributes that must be the same and their data types must be alike. Being compatible does not mean the data types have to be exactly the same. For example, both data types can be used to store numeric values such as NUMBER and SMALLINT as well as character (string) values such as VARCHAR and CHAR. Commonly Used SQL Relational Operators The following are the most common SQL commands used to implement the Union. *Union *Intersect *Minus UNION A union can be used to bring together lists of data in which for example you don't want duplicates. The Union statement combines rows from two or more queries without including duplicate rows. In a way a Union may also be compared to a JOIN in that they are both used to related information from multiple tables. Some issues with Union's is that all corresponding columns need to be of the same data type and only distinct values are selected. Syntax: query UNION query The Union statement combines the output of two Select queries, in which these Select statements must be union-compatible. They must return values with the same attributes and data types. The following is a simple example of what a Union would look like: SELECT CUS_LNAME, CUS_FNAME, CUS_PHONE FROM CUSTOMER UNION SELECT CUS_LNAME, CUS_FNAME, CUS_PHONE FROM CUSTOMER_2; INTERSECT An intersect is good for pointing out what specific values are being duplicated. The Intersect command acts as if it is an AND operator (Value is only selected if in both statements). The Intersect can also generate additional useful information such as displaying customers in a specific zip-code. Syntax: query INTERSECT query A good example of finding a duplicate value would be: SELECT CUS_LNAME, CUS_FNAME, CUS_PHONE FROM CUSTOMER INTERSECT SELECT CUS_LNAME, CUS_FNAME, CUS_PHONE FROM CUSTOMER_2; MINUS The Minus statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not the second. Basically it takes all the results from the first SQL statement, and then subtracts out the ones that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first SQL statement, such results are ignored. Syntax: query MINUS query A good example of combining the rows and only seeing the first row: SELECT CUS_LNAME, CUS_FNAME, CUS_PHONE FROM CUSTOMER_2 MINUS SELECT CUS_LNAME, CUS_FNAME, CUS_PHONE FROM CUSTOMER; References http://www.1keydata.com/sql/sqlunion.html http://www.w3schools.com/sql/sql_union.asp Rob, Peter; Coronel, Carlos; Database Systems: Design, Impllementation and Management; Eighth Edition; Pages 298-303